﻿using System.Collections.Generic;
using NHibernate;
using DomainModel.Entities;
using NHibernate.Criterion;

namespace DAL
{
    public class QueryCriteriaAPI
    {
        private ISession _session;
        public ISession Session
        {
            set
            {
                _session = value;
            }
        }
        public QueryCriteriaAPI(ISession session)
        {
            _session = session;
        }
        #region 语法学习
        /// <summary>
        /// 创建ICriteria实例
        /// </summary>
        /// <returns></returns>
        public IList<Customer> CreateCriteria()
        {
            //NHibernate.ICriteria这个接口代表对一个特定的持久化类的查询。
            //ISession是用来制造Criteria实例的工厂。
            var crit = _session.CreateCriteria(typeof(Customer));
            crit.SetMaxResults(50);
            var customers = crit.List<Customer>();
            return customers;
        }
        /// <summary>
        /// 缩小结果集范围
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Narrowing()
        {
            //一个查询条件(Criterion)是NHibernate.ICriterion接口的一个实例。
            //类NHibernate.Expression.Expression定义了获得一些内置的ICriterion类型的工厂方法。


            var customers = _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Like("Firstname", "YJing%"))
                .Add(Restrictions.Between("Lastname", "A%", "Y%"))
                .List<Customer>();

            //表达式（Expressions）可以按照逻辑分组
            //IList<Customer> customers = _session.CreateCriteria(typeof(Customer))
            //    .Add(Restrictions.Like("Firstname", "YJ%"))
            //    .Add(Restrictions.Or(
            //    Restrictions.Eq("Lastname", "L%"),
            //    Restrictions.IsNull("Lastname")
            //    ))
            //    .List<Customer>();

            //IList<Customer> customers = _session.CreateCriteria(typeof(Customer))
            //    .Add(Restrictions.In("Firstname", new string[] { "YJing", "YJingLee", "Y" }))
            //    .Add(Restrictions.Disjunction()
            //    .Add(Restrictions.IsNull("Lastname"))
            //    .Add(Restrictions.Eq("Lastname", "Lee"))
            //    .Add(Restrictions.Eq("Lastname", "xyz"))
            //    ).List<Customer>();

            //预制的条件类型（Expression的子类）。可以直接嵌入SQL。
            //{alias}是一个占位符，它将会被所查询实体的行别名所替代
            //Parameter paramName = new Parameter("someName", new StringSqlType());
            //IList<Customer> customers = _session.CreateCriteria(typeof(Customer))
            //    .Add(Expression.Sql(
            //      new SqlString(new object[]{
            //          "lower({alias}.Lastname) like lower(","Lastname",")"}), "YJing%", NHibernateUtil.String))
            //          .List<Customer>();
            return customers;
        }
        /// <summary>
        /// 排序Order by
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Order()
        {
            //使用ICriteria.Order对结果集排序,true=asc,false=desc
            return _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Like("Firstname","Y%"))
                .AddOrder(new NHibernate.Criterion.Order("Lastname", true))
                .AddOrder(new NHibernate.Criterion.Order("Firstname", false))
                .List<Customer>();
        }
        #endregion
        #region 实例学习
                /// <summary>
        /// 利用CriteriaAPI按Firstname查询顾客
        /// </summary>
        /// <param name="firstname"></param>
        /// <returns>顾客列表</returns>
        public IList<Customer> UseCriteriaAPI_GetCustomersByFirstname(string firstname)
        {
            //NHibernate1.2写法
            //return _session.CreateCriteria(typeof(Customer))
            //    .Add(new NHibernate.Expression.EqExpression("Firstname", firstname))
            //    .List<Customer>();

            //NHibernate2.0写法
            //return _session.CreateCriteria(typeof(Customer))
            //    .Add(Expression.Eq("Firstname", firstname))
            //    .List<Customer>();
            //使用Name封装：.Add(Restrictions.Eq("Name.Firstname", firstname))
            return _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Eq("Firstname", firstname))
                .List<Customer>();
        }
        /// <summary>
        /// 利用CriteriaAPI按Firstname和Lastname查询顾客
        /// </summary>
        /// <param name="firstname"></param>
        /// <param name="lastname"></param>
        /// <returns></returns>
        public IList<Customer> UseCriteriaAPI_GetCustomersByFirstnameAndLastname(string firstname, string lastname)
        {
            //NHibernate1.2写法
            //return _session.CreateCriteria(typeof(Customer))
            //    .Add(new NHibernate.Expression.EqExpression("Firstname", firstname))
            //    .Add(new NHibernate.Expression.EqExpression("Lastname", lastname))
            //    .List<Customer>();

            //NHibernate2.0写法
            //使用Name封装：.Add(Restrictions.Eq("Firstname", firstname))
            return _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Eq("Firstname", firstname))
                .Add(Restrictions.Eq("Lastname", lastname))
                .List<Customer>();
        }
        /// <summary>
        /// 利用CriteriaAPI获取顾客ID大于CustomerId的顾客
        /// </summary>
        /// <param name="customerId">顾客ID</param>
        /// <returns>顾客列表</returns>
        public IList<Customer> UseCriteriaAPI_GetCutomersWithIdGreaterThan(int customerId)
        {
            //NHibernate1.2写法
            //return _session.CreateCriteria(typeof(Customer))
            //    .Add(new NHibernate.Expression.GtExpression("CustomerId", customerId))
            //    .List<Customer>();

            //NHibernate2.0写法
            return _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Gt("CustomerId", customerId))
                .List<Customer>();
        }
        #endregion
    }
}
